﻿/// Written September 15, 2010 by Joshua A. Schaeffer.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using JasLib;
using JasLib.Extensions;

namespace JasLib.Collections
{
    /*****************************************************************************************/
    public class ExcelCellCollection
    {
        /*****************************************************************************************/
        /// <summary>
        /// This key works properly with both hash and sorted collections.
        /// </summary>
        protected struct CellLocationSortKey : IComparable<CellLocationSortKey>
        {
            public int _iRowIndex;
            public int _iColumnIndex;

            public override bool Equals(object obj)
            {
                if (!(obj is CellLocationSortKey))
                    return false;
                CellLocationSortKey otherKey = (CellLocationSortKey)obj;
                return (_iRowIndex == otherKey._iRowIndex) && (_iColumnIndex == otherKey._iColumnIndex);
            }

            public override int GetHashCode()
            {
                /// Acquire a unique value like an old-school wrap-around bitmap memory buffer.
                long lPretendArrayIndex = (_iRowIndex * ExcelWorksheetModel.MaximumExcel2007Columns) + _iColumnIndex;

                /// The max value is (16384 * 1048576) or 17179869184 which is bigger than int.MaxValue.
                /// ...so we coalesce it in an odd way.
                return (int)(lPretendArrayIndex % (long)int.MaxValue);
            }

            public int CompareTo(CellLocationSortKey other)
            {
                int iComparison = 0;

                /// Compare rows first because there are almost always more rows than columns.
                iComparison = _iRowIndex.CompareTo(other._iRowIndex);
                if (iComparison != 0)
                    return iComparison;

                iComparison = _iColumnIndex.CompareTo(other._iColumnIndex);
                if (iComparison != 0)
                    return iComparison;

                return 0;
            }

            public override string ToString()
            {
                return string.Format("({0}, {1})", _iColumnIndex, _iRowIndex);
            }
        }

        /*****************************************************************************************/
        protected bool _bMetricsValid = true;
        protected Dictionary<CellLocationSortKey, string> _cellDictionary = new Dictionary<CellLocationSortKey, string>();

        /*****************************************************************************************/
        public int CellCount
        {
            get { return _cellDictionary.Count; }
        }

        /*****************************************************************************************/
        public int OccupiedColumnCount
        {
            get
            {
                if (!_bMetricsValid)
                    throw new Exception("Metrics properties currently do not or cannot hold valid values.");
                return (_iMaximumUsedColumn - _iMinimumUsedColumn + 1);
            }
        }

        /*****************************************************************************************/
        public int OccupiedRowCount
        {
            get
            {
                if (!_bMetricsValid)
                    throw new Exception("Metrics properties currently do not or cannot hold valid values.");
                return (_iMaximumUsedRow - _iMinimumUsedRow + 1);
            }
        }

        /*****************************************************************************************/
        public double OccupiedSpaceRatio
        {
            get
            {
                if (!_bMetricsValid)
                    throw new Exception("Metrics properties currently do not or cannot hold valid values.");
                return (double)_cellDictionary.Count / (double)((_iMaximumUsedColumn - _iMinimumUsedColumn + 1) * (_iMaximumUsedRow - _iMinimumUsedRow + 1));
            }
        }

        /*****************************************************************************************/
        private int _iMinimumUsedRow = -1;
        public int MinimumUsedRow
        {
            get
            {
                if (!_bMetricsValid)
                    throw new Exception("Metrics properties currently do not or cannot hold valid values.");
                return _iMinimumUsedRow;
            }
        }

        /*****************************************************************************************/
        private int _iMaximumUsedRow = -1;
        public int MaximumUsedRow
        {
            get
            {
                if (!_bMetricsValid)
                    throw new Exception("Metrics properties currently do not or cannot hold valid values.");
                return _iMaximumUsedRow;
            }
        }

        /*****************************************************************************************/
        private int _iMinimumUsedColumn = -1;
        public int MinimumUsedColumn
        {
            get
            {
                if (!_bMetricsValid)
                    throw new Exception("Metrics properties currently do not or cannot hold valid values.");
                return _iMinimumUsedColumn;
            }
        }

        /*****************************************************************************************/
        private int _iMaximumUsedColumn = -1;
        public int MaximumUsedColumn
        {
            get
            {
                if (!_bMetricsValid)
                    throw new Exception("Metrics properties currently do not or cannot hold valid values.");
                return _iMaximumUsedColumn;
            }
        }

        /*****************************************************************************************/
        public virtual string this[int iColumnIndex, int iRowIndex]
        {
            get
            {
                CellLocationSortKey newLocation;
                newLocation._iColumnIndex = iColumnIndex;
                newLocation._iRowIndex = iRowIndex;

                string strValue;
                if (_cellDictionary.TryGetValue(newLocation, out strValue))
                    return strValue;
                else
                    return string.Empty; /// Simulation of data even though storage is sparse.
            }
            set
            {
                CellLocationSortKey newLocation;
                newLocation._iColumnIndex = iColumnIndex;
                newLocation._iRowIndex = iRowIndex;

                /// Writing a whitespace value has the same effect as deleting the cell.
                string strValue = value.TrimToNull();
                if (strValue == null)
                {
                    if (_cellDictionary.ContainsKey(newLocation))
                    {
                        _cellDictionary.Remove(newLocation);
                        /// Cells are only sorted by full coordinates, not by extremities.
                        /// We can't assume anything at this point,
                        /// and we won't impose a laborious recalculation without the consent of the caller.
                        _bMetricsValid = false;
                    }
                }
                else
                {
                    _cellDictionary.Overwrite(newLocation, strValue);
                    UpdateMetricsWithNextLocation(newLocation);
                }

                return;
            }
        }

        /*****************************************************************************************/
        /// <summary>
        /// A cell indexer that uses standard Excel semantics.
        /// </summary>
        /// <param name="strColumn">A non-case sensitive string identifying the column name (i.e. "A").</param>
        /// <param name="iRow">A one-based row index.</param>
        /// <returns></returns>
        public string this[string strColumn, int iRow]
        {
            get
            {
                return this[ExcelColumnNameToZeroBasedIndex(strColumn), iRow - 1];
            }
            set
            {
                this[ExcelColumnNameToZeroBasedIndex(strColumn), iRow - 1] = value;
            }
        }

        protected static Regex s_excelColumnNameRowNumberRegex = new Regex("(?<column>[A-Za-z]+)(?<row>[0-9]+)", RegexOptions.Compiled | RegexOptions.Singleline);
        /*****************************************************************************************/
        /// <summary>
        /// A cell indexer that uses standard Excel semantics.
        /// </summary>
        public string this[string strColumnRow]
        {
            get
            {
                Match match = s_excelColumnNameRowNumberRegex.Match(strColumnRow.TrimToEmpty());
                if (match.Success)
                    return this[ExcelColumnNameToZeroBasedIndex(match.Groups["column"].Value), int.Parse(match.Groups["row"].Value) - 1];
                else
                    return string.Empty;
            }
            set
            {
                Match match = s_excelColumnNameRowNumberRegex.Match(strColumnRow.TrimToEmpty());
                if (match.Success)
                    this[ExcelColumnNameToZeroBasedIndex(match.Groups["column"].Value), int.Parse(match.Groups["row"].Value) - 1] = value;
            }
        }

        /*****************************************************************************************/
        public void Clear()
        {
            _cellDictionary.Clear();
            _iMinimumUsedColumn = -1;
            _iMaximumUsedColumn = -1;
            _iMinimumUsedRow = -1;
            _iMaximumUsedRow = -1;
            _bMetricsValid = false;
            return;
        }

        /*****************************************************************************************/
        protected void UpdateMetricsWithNextLocation(CellLocationSortKey location)
        {
            if (_cellDictionary.Count == 1)
            {
                _iMinimumUsedColumn = location._iColumnIndex;
                _iMaximumUsedColumn = location._iColumnIndex;
                _iMinimumUsedRow = location._iRowIndex;
                _iMaximumUsedRow = location._iRowIndex;
                _bMetricsValid = true;
            }

            /// If the metrics aren't valid, then this new data won't be legit anyway.
            else if (_bMetricsValid)
            {
                if (location._iColumnIndex < _iMinimumUsedColumn)
                    _iMinimumUsedColumn = location._iColumnIndex;
                else if (location._iColumnIndex > _iMaximumUsedColumn)
                    _iMaximumUsedColumn = location._iColumnIndex;
                if (location._iRowIndex < _iMinimumUsedRow)
                    _iMinimumUsedRow = location._iRowIndex;
                else if (location._iRowIndex > _iMaximumUsedRow)
                    _iMaximumUsedRow = location._iRowIndex;
            }
            return;
        }

        /*****************************************************************************************/
        public bool UpdateMetrics()
        {
            /// Without any cells, it's impossible for cell metrics to have valid data.
            if (_cellDictionary.Count == 0)
            {
                _bMetricsValid = false;
                return false;
            }

            foreach (CellLocationSortKey location in _cellDictionary.Keys)
                UpdateMetricsWithNextLocation(location);

            _bMetricsValid = true;
            return true;
        }

        /*****************************************************************************************/
        public static int ExcelColumnNameToZeroBasedIndex(string strColumn)
        {
            string strParseable = strColumn.Trim().ToLower();

            int iSum = 0;
            int iPower = 1;

            /// We are essentially converting a base-26 number.
            /// So we loop from the least-significant digit to the most-significant.
            for (int iIndex = strParseable.Length - 1; iIndex >= 0; iIndex--)
            {
                char chThis = strParseable[iIndex];
                if (!chThis.InRange('a', 'z'))
                    throw new ArgumentException("Excel column name can only contain letters ranging from A to Z.");

                iSum += (chThis - 'a' + 1) * iPower;
                iPower *= 26;
            }

            return (iSum - 1);
        }

        /*****************************************************************************************/
        public static string ZeroBasedIndexToExcelColumnName(int iIndex)
        {
            return iIndex.ToStringOfBase("ABCDEFGHIJKLMNOPQRSTUVWXYZ", false);
        }

        /*****************************************************************************************/
        /// <summary>
        /// http://www.cpearson.com/excel/datetime.htm
        /// </summary>
        /// <param name="bUse1904Mode">Whether to serialize the date starting from 1904-Jan-0 as opposed to 1900-Jan-0.</param>
        /// <param name="bTimeOnly">If true, then only the TimeSpan variable is valid. If false, then only the DateTime variable is valid.</param>
        public static bool TryParseExcelDateTime(
            string strInput,
            bool bUse1904Mode,
            ref bool bTimeOnly,
            ref DateTime dateTime,
            ref TimeSpan timeSpan)
        {
            if (strInput == null)
                return false;

            string strTrimmed = strInput.Trim();
            if (strTrimmed == string.Empty)
                return false;

            string[] dateTimeStrings = strInput.Split(".");
            if (dateTimeStrings.Length > 2)
                return false;

            /// First string (date) will always be there.
            /// It is a positive offset of days.
            int iDateComponent;
            if (!int.TryParse(dateTimeStrings[0], out iDateComponent) || iDateComponent < 0)
                return false;

            dateTime = bUse1904Mode ? new DateTime(1904, 1, 1) : new DateTime(1900, 1, 1);
            bTimeOnly = (iDateComponent == 0);
            if (!bTimeOnly)
                dateTime += TimeSpan.FromDays(iDateComponent - 1);

            /// Second string (time) is optional.
            /// It is the fraction of the day in decimal.
            if (dateTimeStrings.Length > 1)
            {
                /// Trailing zeroes are superfluous.
                string strTimeComponent = "0." + dateTimeStrings[1].TrimStart().TrimEnd('0');

                double dTimeComponent;
                if (!double.TryParse(strTimeComponent, out dTimeComponent) || dTimeComponent < 0d)
                    return false;

                timeSpan = TimeSpan.FromDays(dTimeComponent);
                dateTime += timeSpan;
            }

            return true;
        }
    }

    /*****************************************************************************************/
    public class ExcelWorksheetModel : ExcelCellCollection
    {
        // http://msdn.microsoft.com/en-us/library/aa730921(office.12).aspx
        public static readonly int MaximumExcel2003Columns = 256;
        public static readonly int MaximumExcel2003Rows = 65536;
        public static readonly int MaximumExcel2007Columns = 16384;
        public static readonly int MaximumExcel2007Rows = 1048576;

        internal ExcelSpreadsheetModel _ownerSpreadsheet = null;

        private string _strName = string.Empty;
        private static readonly char[] s_invalidNameCharacters = ":\\/?*[]".ToCharArray();

        /*****************************************************************************************/
        /// <summary>
        /// This value is always trimmed.
        /// </summary>
        public string Name
        {
            get
            {
                return _strName;
            }
            set
            {
                string strNewValue = value.TrimToNull();
                if (strNewValue == null)
                    throw new Exception("Worksheet name cannot be blank.");

                if (strNewValue.Length > 31)
                    throw new Exception("Worksheet name cannot exceed 31 characters in length.");

                if (strNewValue.IndexOfAny(s_invalidNameCharacters) != -1)
                    throw new Exception("Worksheet name cannot contain any of the following characters:  :  \\  /  ?  *  [  or  ]");

                if (_ownerSpreadsheet != null)
                {
                    if (_ownerSpreadsheet.Worksheets.ContainsName(strNewValue))
                        throw new ArgumentException("Worksheet name already exists in collection.");
                }

                string strOldValue = _strName;
                _strName = strNewValue;

                if (_ownerSpreadsheet != null)
                {
                    _ownerSpreadsheet.Worksheets.RenameInMap(strOldValue, _strName);
                }

                return;
            }
        }

        /*****************************************************************************************/
        public override string this[int iColumn, int iRow]
        {
            get
            {
                /// If bound to a spreadsheet, then cell addressing must obey constraints.
                if (_ownerSpreadsheet != null)
                {
                    if (iColumn < 0 || _ownerSpreadsheet._iSharedMaximumColumnCount <= iColumn)
                        throw new ArgumentOutOfRangeException("iColumn");
                    if (iRow < 0 || _ownerSpreadsheet._iSharedMaximumRowCount <= iRow)
                        throw new ArgumentOutOfRangeException("iRow");
                }

                return base[iColumn, iRow];
            }
            set
            {
                /// If bound to a spreadsheet, then cell addressing must obey constraints.
                if (_ownerSpreadsheet != null)
                {
                    if (iColumn < 0 || _ownerSpreadsheet._iSharedMaximumColumnCount <= iColumn)
                        throw new ArgumentOutOfRangeException("iColumn");
                    if (iRow < 0 || _ownerSpreadsheet._iSharedMaximumRowCount <= iRow)
                        throw new ArgumentOutOfRangeException("iRow");
                }

                base[iColumn, iRow] = value;
            }
        }

        /*****************************************************************************************/
        public bool Load(Microsoft.Office.Interop.Excel.Worksheet xlWorksheet)
        {
            Clear();

            try
            {
                Name = xlWorksheet.Name;

                foreach (Microsoft.Office.Interop.Excel.Range cell in EnumUsedCells(xlWorksheet))
                {
                    /// The formula is used to generate the text. If it exists, the text is irrelevant.
                    string strFinalValue = (string)cell.Formula;

                    /// No formula, so maybe Excel is saving the text as a constant.
                    if (strFinalValue.IsNullOrWhiteSpace())
                        strFinalValue = (string)cell.Text;

                    if (!strFinalValue.IsNullOrWhiteSpace())
                    {
                        int iColumn = ((int)cell.Column - 1);
                        int iRow = ((int)cell.Row - 1);

                        /// It is debatable whether trimming will interfere with any operations.
                        this[iColumn, iRow] = strFinalValue.Trim();
                    }
                }
            }
            catch
            {
                return false;
            }

            return true;
        }

        /*****************************************************************************************/
        public bool Export(Microsoft.Office.Interop.Excel.Worksheet xlWorksheet, bool bExportAllAsStringConstants)
        {
            try
            {
                /// Kill all the existing cells.
                foreach (Microsoft.Office.Interop.Excel.Range cell in EnumUsedCells(xlWorksheet))
                {
                    cell.Delete(false);
                }

                if (bExportAllAsStringConstants)
                    xlWorksheet.Cells.NumberFormat = "@";

                foreach (KeyValuePair<CellLocationSortKey, string> kvp in _cellDictionary)
                {
                    int iColumnIndex = (kvp.Key._iColumnIndex + 1);
                    int iRowIndex = (kvp.Key._iRowIndex + 1);

                    /*
                    /// I'm not convinced that cell-by-cell is the best way to do this.
                    if (bExportAllAsStringConstants)
                    {
                        object objCell = xlWorksheet.Cells[iRowIndex, iColumnIndex];
                        xlWorksheet.get_Range(objCell, objCell).Cells.NumberFormat = "@";
                    }
                    */

                    xlWorksheet.Cells[iRowIndex, iColumnIndex] = kvp.Value;
                }
            }
            catch
            {
                return false;
            }

            return true;
        }

        /*****************************************************************************************/
        public override string ToString()
        {
            return string.Format("{0} ({1} cell(s) total)", Name, CellCount);
        }

        /*****************************************************************************************/
        /// <summary>
        /// This wrapper function is necessary because Range.SpecialCells() throws an exception
        /// if no cells would be returned. It would be a good candidate for an extension method.
        /// </summary>
        public static IEnumerable<Microsoft.Office.Interop.Excel.Range> SafeEnumSpecialCells(
            /* this */ Microsoft.Office.Interop.Excel.Range sourceRange,
            Microsoft.Office.Interop.Excel.XlCellType cellType,
            object objValue)
        {
            Microsoft.Office.Interop.Excel.Range results = null;

            try
            {
                /// We can't do "yield return" inside a try block if it also includes a catch. :(
                try
                {
                    /// REMINDER: If you are debugging with all exceptions trapped upon throw
                    /// and you're staring at this line,
                    /// the "No cells were found" COMException is normal and expected.
                    /// Badly designed by Microsoft but perfectly fine.
                    results = sourceRange.SpecialCells(cellType, objValue ?? System.Reflection.Missing.Value /* these functions don't handle nulls well */);
                }
                catch (COMException e)
                {
                    /// The only tolerable error is "No cells were found."
                    if (e.ErrorCode != -2146827284)
                        yield break;
                }

                if (results != null)
                {
                    foreach (Microsoft.Office.Interop.Excel.Range cell in results)
                        yield return cell;
                }
            }
            finally
            {
                if (results != null)
                {
                    results = null;
                }
            }

            yield break;
        }

        /*****************************************************************************************/
        /// <summary>
        /// Enumerates all cells containing text ("formulae & constants").
        /// http://www.ozgrid.com/VBA/special-cells.htm
        /// </summary>
        /// <param name="xlWorksheet"></param>
        /// <returns></returns>
        public static IEnumerable<Microsoft.Office.Interop.Excel.Range> EnumUsedCells(Microsoft.Office.Interop.Excel.Worksheet xlWorksheet)
        {
            foreach (Microsoft.Office.Interop.Excel.Range cell in SafeEnumSpecialCells(xlWorksheet.UsedRange, Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, null))
                yield return cell;
            foreach (Microsoft.Office.Interop.Excel.Range cell in SafeEnumSpecialCells(xlWorksheet.UsedRange, Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeFormulas, null))
                yield return cell;

            yield break;
        }
    }

    /*****************************************************************************************/
    public class ExcelSpreadsheetModel : IDisposable
    {
        // These constraints must be common to the entire spreadsheet.
        internal int _iSharedMaximumColumnCount = ExcelWorksheetModel.MaximumExcel2007Columns;
        internal int _iSharedMaximumRowCount = ExcelWorksheetModel.MaximumExcel2007Rows;

        /*****************************************************************************************/
        [DebuggerDisplay("Count = {Count}")]
        public class WorksheetCollection : SimplifiedListBase<ExcelWorksheetModel>
        {
            private ExcelSpreadsheetModel _ownerSpreadsheet = null;
            private List<ExcelWorksheetModel> _worksheetList = new List<ExcelWorksheetModel>();
            private Dictionary<string, ExcelWorksheetModel> _nameToWorksheetMap = new Dictionary<string, ExcelWorksheetModel>();

            public WorksheetCollection(ExcelSpreadsheetModel ownerSpreadsheet)
            {
                _ownerSpreadsheet = ownerSpreadsheet;
                return;
            }

            public override IEnumerator<ExcelWorksheetModel> GetEnumerator()
            {
                return _worksheetList.GetEnumerator();
            }

            public override int Count
            {
                get { return _worksheetList.Count; }
            }

            public override void Add(ExcelWorksheetModel newItem)
            {
                Insert(Count, newItem);
                return;
            }

            public override void Clear()
            {
                _worksheetList.Clear();
                _nameToWorksheetMap.Clear();
                return;
            }

            public override bool Contains(ExcelWorksheetModel item)
            {
                return _worksheetList.Contains(item);
            }

            public bool ContainsName(string strName)
            {
                return (GetWorksheetByName(strName) != null);
            }

            public override int IndexOf(ExcelWorksheetModel item)
            {
                return _worksheetList.IndexOf(item);
            }

            public override void Insert(int index, ExcelWorksheetModel newItem)
            {
                if (index < 0)
                    index = 0;

                ProposeInsertModel(newItem);

                _nameToWorksheetMap.Add(newItem.Name.ToLower(), newItem);
                _worksheetList.Insert(index, newItem);
                newItem._ownerSpreadsheet = _ownerSpreadsheet;
                return;
            }

            public override bool Remove(ExcelWorksheetModel item)
            {
                _nameToWorksheetMap.Remove(item.Name.ToLower());
                _worksheetList.Remove(item);
                item._ownerSpreadsheet = null;
                return true;
            }

            public override void RemoveAt(int index)
            {
                ExcelWorksheetModel model = _worksheetList[index];
                _worksheetList.RemoveAt(index);
                _nameToWorksheetMap.Remove(model.Name.ToLower());
                model._ownerSpreadsheet = null;
                return;
            }

            public override ExcelWorksheetModel this[int iIndex]
            {
                get
                {
                    return _worksheetList[iIndex];
                }
                set
                {
                    /// This will handle the range check automatically.
                    ExcelWorksheetModel priorModel = _worksheetList[iIndex];

                    string strOldNameKey = priorModel.Name.ToLower();
                    string strNewNameKey = value.Name.ToLower();

                    if (strOldNameKey == strNewNameKey)
                    {
                        _nameToWorksheetMap[strOldNameKey] = value;
                    }
                    else
                    {
                        ProposeInsertModel(value);

                        _nameToWorksheetMap.Remove(strOldNameKey);
                        _nameToWorksheetMap.Add(strNewNameKey, value);
                    }

                    value._ownerSpreadsheet = _ownerSpreadsheet;
                    _worksheetList[iIndex] = value;
                    priorModel._ownerSpreadsheet = null;
                    return;
                }
            }

            public ExcelWorksheetModel this[string strName]
            {
                get
                {
                    return GetWorksheetByName(strName);
                }
            }

            public ExcelWorksheetModel GetWorksheetByName(string strName)
            {
                string strTrimmed = strName.Trim();
                string strNameKey = strTrimmed.ToLower();
                ExcelWorksheetModel worksheet = null;

                if (_nameToWorksheetMap.TryGetValue(strNameKey, out worksheet))
                    return worksheet;

                /// Fallback repair option in case things somehow got screwed up.
                /// This won't be a performance problem unless the caller blindly spams names to look up.
                worksheet = _worksheetList.Find(wks => wks.Name == strTrimmed);
                if (worksheet != null)
                {
                    _nameToWorksheetMap.Add(strNameKey, worksheet);
                }
                return worksheet;
            }

            internal void ProposeInsertModel(ExcelWorksheetModel newItem)
            {
                /// Bullet-proof.
                if (newItem._ownerSpreadsheet == _ownerSpreadsheet || _worksheetList.Contains(newItem))
                    throw new ArgumentException("Worksheet instance already present in collection.");

                if (_nameToWorksheetMap.ContainsKey(newItem.Name))
                    throw new ArgumentException("Worksheet name already exists in collection.");

                if (newItem.UpdateMetrics())
                {
                    if (newItem.MaximumUsedColumn >= _ownerSpreadsheet._iSharedMaximumColumnCount)
                        throw new ArgumentException("Worksheet has too many columns and is incompatible with this spreadsheet.");

                    if (newItem.MaximumUsedRow >= _ownerSpreadsheet._iSharedMaximumRowCount)
                        throw new ArgumentException("Worksheet has too many rows and is incompatible with this spreadsheet.");
                }
            }

            internal void RenameInMap(string strOldName, string strNewName)
            {
                _nameToWorksheetMap.ChangeKey(strOldName, strNewName.ToLower());
                return;
            }

            /// <summary>
            /// This returns the worksheet with the highest number of cells.
            /// This can be very useful when importing from a spreadsheet that has one full tab and two empty ones,
            /// without needing to assume an integer worksheet index (usually 1).
            /// </summary>
            public ExcelWorksheetModel MostPopulatedWorksheet
            {
                get
                {
                    if (_worksheetList.Count == 0)
                        return null;

                    ExcelWorksheetModel winnerWorksheet = _worksheetList[0];
                    for (int iIndex = 1; iIndex < _worksheetList.Count; iIndex++)
                    {
                        if (_worksheetList[iIndex].CellCount > winnerWorksheet.CellCount)
                            winnerWorksheet = _worksheetList[iIndex];
                    }

                    return (winnerWorksheet.CellCount > 0) ? winnerWorksheet : null;
                }
            }
        }

        /*****************************************************************************************/
        private WorksheetCollection _worksheets = null;
        public WorksheetCollection Worksheets
        {
            get { return _worksheets; }
        }

        /*****************************************************************************************/
        public ExcelSpreadsheetModel()
        {
            _worksheets = new WorksheetCollection(this);
            return;
        }

        /*****************************************************************************************/
        public ExcelSpreadsheetModel(string strFileName)
            : this()
        {
            if (!Load(strFileName))
                throw new Exception("Unable to load.");
            return;
        }

        /*****************************************************************************************/
        ~ExcelSpreadsheetModel()
        {
            return;
        }

        /*****************************************************************************************/
        public void Dispose()
        {
            GC.SuppressFinalize(this);
            return;
        }

        /*****************************************************************************************/
        public bool Load(string strFileName)
        {
            if (!File.Exists(strFileName))
                return false;

            Clear();

            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;

            try
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();

                xlWorkbook = xlApp.Workbooks.Open(
                    strFileName, 0, true, 5, string.Empty, string.Empty, false,
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                    string.Empty, false, false, 0, false, false, false);

                foreach (Microsoft.Office.Interop.Excel.Worksheet xlWorksheet in xlWorkbook.Worksheets)
                {
                    ExcelWorksheetModel newWorksheet = new ExcelWorksheetModel();
                    if (!newWorksheet.Load(xlWorksheet))
                        return false;

                    _worksheets.Add(newWorksheet);
                }
            }
            catch
            {
                Clear();
                return false;
            }
            finally
            {
                if (xlWorkbook != null)
                {
                    xlWorkbook = null;
                }

                if (xlApp != null)
                {
                    xlApp = null;
                }
            }

            return true;
        }

        /*****************************************************************************************/
        public bool Export(Microsoft.Office.Interop.Excel.Workbook xlWorkbook, bool bExportAllAsStringConstants)
        {
            if (_worksheets.Count == 0)
                return false;

            try
            {
                /// First clean out all default/preexisting worksheets.
                /// Only catch is that at least one must exist at all times.
                while (xlWorkbook.Sheets.Count > 1)
                {
                    (xlWorkbook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet).Delete();
                }

                /// Rename the first worksheet to match.
                Microsoft.Office.Interop.Excel.Worksheet priorWorksheet = (xlWorkbook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet);
                priorWorksheet.Name = _worksheets[0].Name;

                /// Insert and name every worksheet after the first one.
                for (int iIndex = 1; iIndex < _worksheets.Count; iIndex++)
                {
                    priorWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets.Add(System.Reflection.Missing.Value, priorWorksheet, 1, System.Reflection.Missing.Value);
                    priorWorksheet.Name = _worksheets[iIndex].Name;
                }

                /// Dump the cell data to every worksheet.
                for (int iIndex = 1; iIndex <= xlWorkbook.Sheets.Count; iIndex++)
                {
                    if (!_worksheets[iIndex - 1].Export(xlWorkbook.Sheets[iIndex] as Microsoft.Office.Interop.Excel.Worksheet, bExportAllAsStringConstants))
                        return false;
                }
            }
            catch
            {
                return false;
            }
            finally
            {
            }

            return true;
        }

        /*****************************************************************************************/
        public bool ExportToNewLiveInstance(bool bExportAllAsStringConstants)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;

            try
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkbook = xlApp.Workbooks.Add(System.Reflection.Missing.Value);
                if (Export(xlWorkbook, bExportAllAsStringConstants))
                    xlApp.Visible = true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (xlWorkbook != null)
                {
                    xlWorkbook = null;
                }

                if (xlApp != null)
                {
                    xlApp = null;
                }
            }

            return true;
        }

        /*****************************************************************************************/
        public bool SaveAs(string strFileName, bool bExportAllAsStringConstants)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;

            try
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkbook = xlApp.Workbooks.Add(System.Reflection.Missing.Value);
                if (Export(xlWorkbook, bExportAllAsStringConstants))
                    xlApp.Save(strFileName);
            }
            catch
            {
                return false;
            }
            finally
            {
                if (xlWorkbook != null)
                {
                    xlWorkbook = null;
                }

                if (xlApp != null)
                {
                    xlApp = null;
                }
            }

            return true;
        }

        /*****************************************************************************************/
        public void Clear()
        {
            _worksheets.Clear();
            return;
        }
    }
}
